﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Collections.ObjectModel;
using HotelSMSSystem.Objects;
using HotelSMSSystem.Utils;

namespace HotelSMSSystem
{

    public class clsDatabase
    {
        public static string _strConnect = Common.GetConfig("connstr");

        private MySQLHelper _mySQLHelper = null;
        public MySQLHelper MySQLHelper
        {
            get { return _mySQLHelper; }
        }

        public clsDatabase()
        {
            _mySQLHelper = new MySQLHelper(_strConnect);

        }

        public void Open()
        {
            _mySQLHelper.Open();
        }

        public void Close()
        {
            _mySQLHelper.Close();
        }

        /// <summary>
        /// Đọc danh sách cấu hình gửi tin nhắn
        /// </summary>
        /// <returns></returns>
        public Collection<ConfigInfo> GetConfigs()
        {
            Collection<ConfigInfo> configs = new Collection<ConfigInfo>();
            try
            {
                string sql = "select h.name, h.tbl_postfix, c.* from tbl_config c join tbl_hotel h on h.id = c.hotel_id where c.active=1 and flg_sms=1 and send_time=" + DateTime.Now.Hour + " and last_sent != '" + DateTime.Today.ToString("yyyy-MM-dd") + "'";
                DataTable dt = _mySQLHelper.ExecuteQuery(sql);
                if (dt != null)
                {
                    foreach (DataRow r in dt.Rows)
                    {
                        ConfigInfo cfg = new ConfigInfo();
                        cfg.ID = Convert.ToInt32(r["id"]);
                        cfg.HotelID = Convert.ToInt32(r["hotel_id"]);
                        cfg.PostFix = Convert.ToInt32(r["tbl_postfix"]);
                        cfg.HotelName = r["name"].ToString();
                        cfg.FeeSaturday = Convert.ToInt32(r["fee_saturday"]);
                        cfg.FeeSunday = Convert.ToInt32(r["fee_sunday"]);
                        cfg.SendTime = Convert.ToInt32(r["send_time"]);
                        cfg.ToTotal = r["to_total"].ToString();
                        cfg.FlgInDay = Convert.ToInt32(r["flg_inday"]);
                        cfg.Phone = r["phone"].ToString().Split(',');

                        if (cfg.FlgInDay == 0)
                        {
                            // Trường hợp xem ngày hôm trước
                            cfg.To = DateTime.Now;
                            cfg.From = cfg.To.AddDays(-1);
                        }
                        else
                        {
                            // Trường hợp xem 24h trước
                            cfg.From = DateTime.ParseExact(DateTime.Now.AddDays(-1).ToString("ddMMyyyy") + "000000", "ddMMyyyyHHmmss", null);
                            cfg.To = cfg.From.AddSeconds(24 * 3600 - 1);
                        }

                        configs.Add(cfg);
                    }
                }
            }
            catch (Exception e)
            {
                Logger.Append("DB", "ERROR", "GetConfigs: " + e.Message);
                configs = new Collection<ConfigInfo>();
            }
            return configs;
        }

        /// <summary>
        /// Hàm cập nhật lại thời gian gửi tin nhắn trong ngày
        /// </summary>
        /// <param name="id">config id</param>
        public void UpdateConfig(int id)
        {
            try
            {
                string sql = "update tbl_config set last_sent = '" + DateTime.Today.ToString("yyyy-MM-dd") + "' where id = " + id;
                _mySQLHelper.ExecuteNonQuery(sql);
            }
            catch (Exception e)
            {
                Logger.Append("DB", "ERROR", "UpdateConfig ID: " + id + ": " + e.Message);
            }
        }

        /// <summary>
        /// Tính tổng chi
        /// </summary>
        /// <param name="config"></param>
        /// <returns></returns>
        public double GetHotelPay(ConfigInfo config)
        {
            double pay = 0;
            try
            {
                string sql = "select sum(money) from tbl_pay where hotel_id = " + config.HotelID + " and date_pay >= '" + config.From.ToString("yyyy-MM-dd HH:mm:ss") + "' and date_created <= '" + config.To.ToString("yyyy-MM-dd HH:mm:ss") + "'";
                string sPay = _mySQLHelper.ExecuteScalar(sql).ToString();
                if (!string.IsNullOrEmpty(sPay))
                    pay = Convert.ToDouble(sPay);
            }
            catch (Exception e)
            { 
                pay = 0;
                Logger.Append("DB", "ERROR", "GetHotelPay: " + e.Message);
            }
            return pay;
        }

        /// <summary>
        /// Tính tổng thu
        /// </summary>
        /// <param name="config"></param>
        /// <returns></returns>
        public double GetHotelIncome(ConfigInfo config)
        {
            double income = 0;
            try
            {
                string sql = "select sum(h.totalPrice) as pprice "
                            + "from tbl_history_book_room_" + config.PostFix + " h join "
                            + "(select r.id, t.hotel_id "
                            + "from tbl_room r join tbl_room_type t on r.room_type = t.id "
                            + "join tbl_hotel h on t.hotel_id = h.id "
                            + "where r.active = 1 and h.active = 1 and t.active = 1 and h.id = " + config.HotelID + ") a on a.id = h.room_id "
                            + "where h.timeend >= '" + config.From.ToString("yyyy-MM-dd HH:mm:ss") + "' and h.timeend <= '" + config.To.ToString("yyyy-MM-dd HH:mm:ss") +"'";
                string sIncome = _mySQLHelper.ExecuteScalar(sql).ToString();
                if (!string.IsNullOrEmpty(sIncome))
                    income = Convert.ToDouble(sIncome);
            }
            catch (Exception e)
            {
                income = 0;
                Logger.Append("DB", "ERROR", "GetHotelIncome: " + e.Message);
            }
            return income;
        }
    }
}